package jwr.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import jwr.interfaces.SungJukService;
import jwr.model.SungJukVO;

public class JDBCSungJuk implements SungJukService{
	private static final String DRV = "oracle.jdbc.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:@//localhost:1521/xe";
	private static final String USR = "scott";
	private static final String PWD = "TIGER";
	
	private static final String insertSQL ="insert into cdinfo VALUES(?,?,?,?,?)";
	private static final String selectSQL ="select * from sungjuk order by sjno desc";
	
	private static final String updateSQL = "update sungjuk set name =?,kor=?,eng=?,mat=? where sjno = ?" ;
	private static final String deleteSQL = "delete from sungjuk where sjno = ?";
	
	@Override
	public boolean newSungJuk(SungJukVO sj) {
		Connection conn = null;
		PreparedStatement ps = null;
		boolean result = false;
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(insertSQL);
			// ? 매개변수에 들어갈 실제 입력값을 지정
			ps.setString(1, sj.getName());
			ps.setInt(2, sj.getKor());
			ps.setInt(3, sj.getEng());
			ps.setInt(4, sj.getMat());
			// 성공적으로 insert 되면 result를 true로
			int cnt = ps.executeUpdate();
			// 성공적으로 insert 되면 result를 true로 바꿈
			if (cnt>0) result = true;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(ps != null) 
				try{ps.close();}catch(Exception e){ }
			if(conn != null)
				try{conn.close();} catch(Exception e){ }
		}
		return result;
	}

	@Override
	public String readsSungJuk() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String result = "";
		
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(selectSQL);
			rs = ps.executeQuery();
			// selectSQL 실행결과를 모두 출력
			while(rs.next()){
				for(int i =1 ;i<=9;i++){
					result += rs.getString(i)+" ";
				}
				result+="\n";
			}
		} catch (Exception e) {
			
		} finally{
			if(rs != null) try{rs.close();} catch(Exception e){}
			if(ps != null) try{ps.close();} catch(Exception e){}
			if(conn != null) try{conn.close();} catch(Exception e){}
			// 생성 할 때 conn먼저 만들고 ps 만들고 rs를 만들었기 때문에 역순으로 닫는다.
			// 만든 역순으로 닫음
		}
		return result;
	}

	@Override
	public boolean modifySungJuk(SungJukVO sj, String key) {
		boolean result = false;
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(updateSQL);
			ps.setString(1, sj.getName());
			ps.setInt(2, sj.getKor());
			ps.setInt(3, sj.getEng());
			ps.setInt(4, sj.getMat());
			ps.setString(5, key);
			int cnt = ps.executeUpdate();
			if(cnt>0){
				result = true;
			}
		} catch (Exception e) {
		}finally{
			if(ps!=null)try{ps.close();} catch(Exception e){}
			if(conn!=null)try{conn.close();} catch(Exception e){}
		}
		return result;
	}

	@Override
	public boolean removeSungJuk(String key) {
		Connection conn = null;
		PreparedStatement ps = null;
		boolean result = false;
		
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(deleteSQL);
			ps.setString(1, key);
			int cnt = ps.executeUpdate();
			if(cnt>0){
				result = true;
			}
		} catch (Exception e) {
			
		} finally{
			if(ps!=null) try { ps.close();	} catch (Exception e2) {	}
			if(conn!=null) try { conn.close();}catch(Exception e3) { }
		}
		return result;
	}

}
